package com.ssy.lingxi.report.dao.purchase;

import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseNeedPlanStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 *  需求计划DAO
 * @author ds
 * @date 2021/6/3
 * @version 2.0.0
 */
@Service
public class NeedPlanDAO {

    @Resource
    @Qualifier("purchaseJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 需求计划-统计数量
     * @return
     */
    public List<PurchaseNeedPlanStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" member_id memberId,role_id roleId,")
                .append(" sum(case when (inner_status in (1,8,9,10))then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (inner_status = 2 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (inner_status = 3 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (inner_status = 4 )then 1 else 0 end) toBeCommitCount")
                .append(" FROM pu_need_plan ")
                .append(" group by member_id,role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseNeedPlanStatisticsTallyVO.class));
    }


}
